-- 营销活动主题插入数据
-- 开启本地模式
set hive.exec.mode.local.auto=false;
-- set hive.exec.mode.local.auto.inputbytes.max=500000000;
-- 用户启动，昨天
insert overwrite table dws_nshop.dws_nshop_ulog_launch
partition(bdp_day='${yesterday}')
select
    collect_set(user_id)[0],
    collect_set(device_num)[0],
    collect_set(device_type)[0],
    collect_set(os)[0],
    collect_set(os_version)[0],
    collect_set(manufacturer)[0],
    collect_set(carrier)[0],
    collect_set(network_type)[0],
    collect_set(area_code)[0],
    count(*)
from dwd_nshop.dwd_nshop_actlog_lauch
where bdp_day='${yesterday}'
group by device_num;
-- 用户启动，7天
insert overwrite table dws_nshop.dws_nshop_ulog_launch_7d
partition (bdp_day='${yesterday}')
select collect_set(user_id)[0],
       collect_set(device_num)[0],
       collect_set(device_type)[0],
       collect_set(os)[0],
       collect_set(os_version)[0],
       collect_set(manufacturer)[0],
       collect_set(carrier)[0],
       collect_set(network_type)[0],
       collect_set(area_code)[0],
       count(*)
from dwd_nshop.dwd_nshop_actlog_lauch
where bdp_day between '${weekF}' and '${yesterday}'
group by device_num;
-- 用户浏览
insert overwrite table dws_nshop.dws_nshop_ulog_view
    partition (bdp_day = '${yesterday}')
select collect_set(user_id)[0],
       collect_set(device_num)[0],
       collect_set(device_type)[0],
       collect_set(os)[0],
       collect_set(os_version)[0],
       collect_set(manufacturer)[0],
       collect_set(carrier)[0],
       collect_set(network_type)[0],
       collect_set(area_code)[0],
       count(device_num)
from dwd_nshop.dwd_nshop_actlog_pdtview
where bdp_day = '${yesterday}'
group by device_num;
insert overwrite table dws_nshop.dws_nshop_ulog_view_7d
    partition (bdp_day = '${yesterday}')
select collect_set(user_id)[0],
       collect_set(device_num)[0],
       collect_set(device_type)[0],
       collect_set(os)[0],
       collect_set(os_version)[0],
       collect_set(manufacturer)[0],
       collect_set(carrier)[0],
       collect_set(network_type)[0],
       collect_set(area_code)[0],
       count(device_num)
from dwd_nshop.dwd_nshop_actlog_pdtview
where bdp_day between '${weekF}' and '${yesterday}'
group by device_num;

-- 用户查询
insert overwrite table dws_nshop.dws_nshop_ulog_search partition(bdp_day='${yesterday}')
select
    collect_set(user_id)[0],
    collect_set(device_num)[0] ,
    collect_set(device_type)[0],
    collect_set(os)[0]  ,
    collect_set(os_version)[0] ,
    collect_set(manufacturer)[0],
    collect_set(carrier)[0] ,
    collect_set(network_type)[0],
    collect_set(area_code)[0] ,
    count(device_num)
from dwd_nshop.dwd_nshop_actlog_pdtsearch
where bdp_day='${yesterday}'
group by device_num;
insert overwrite table dws_nshop.dws_nshop_ulog_search partition(bdp_day='${yesterday}')
select
    collect_set(user_id)[0],
    collect_set(device_num)[0] ,
    collect_set(device_type)[0],
    collect_set(os)[0]  ,
    collect_set(os_version)[0] ,
    collect_set(manufacturer)[0],
    collect_set(carrier)[0] ,
    collect_set(network_type)[0],
    collect_set(area_code)[0] ,
    count(device_num)
from dwd_nshop.dwd_nshop_actlog_pdtsearch
where bdp_day between '${weekF}' and '${yesterday}'
group by device_num;
-- 用户关注
insert overwrite table dws_nshop.dws_nshop_ulog_comment
    partition (bdp_day='${yesterday}')
select
    collect_set(user_id)[0],
    device_num,
    collect_set(device_type)[0],
    collect_set(os)[0],
    collect_set(os_version)[0],
    collect_set(manufacturer)[0],
    collect_set(carrier)[0],
    collect_set(network_type)[0],
    collect_set(area_code)[0],
    count(target_id),
    count(distinct target_id),
    collect_set(ct)[0]
from dwd_nshop.dwd_actlog_product_comment
where bdp_day = '${yesterday}'
group by device_num;
-- 用户关注7day
insert overwrite table dws_nshop.dws_nshop_ulog_comment partition (bdp_day='${yesterday}')
select
    collect_set(user_id)[0],
    collect_set(device_num)[0],
    collect_set(device_type)[0],
    collect_set(os)[0],
    collect_set(os_version)[0],
    collect_set(manufacturer)[0],
    collect_set(carrier)[0],
    collect_set(network_type)[0],
    collect_set(area_code)[0],
    count(target_id),
    count(distinct target_id),
    collect_set(ct)[0]
from dwd_nshop.dwd_actlog_product_comment
where bdp_day between '${weekF}' and '${yesterday}'
group by device_num;

-- 用户交易记录宽表 dws_nshop_user_orders
with t1 as(
    select
        a.customer_id,
        a.order_id,
        a.district_money,
        a.shipping_money,
        b.customer_natives,
        a.payment_money
    from dwd_nshop.dwd_nshop_orders_details a
             join ods_nshop.ods_02_customer b
                  on a.customer_id=b.customer_id
    where bdp_day='${yesterday}'
)
insert overwrite table dws_nshop.dws_nshop_user_orders partition(bdp_day='${yesterday}')
select
    customer_id,
    customer_natives,
    count(order_id),
    sum(payment_money),
    sum(shipping_money),
    sum(district_money),
    current_timestamp() ct
from t1
group by customer_id, customer_natives;
-- 用户投诉订单宽表 dws_nshop_user_complainant
with t1 as (
    select a.customer_id,
           a.order_id,
           a.payment_money,
           a.supplier_code,
           b.customer_natives
    from dwd_nshop.dwd_nshop_orders_details a
             join ods_nshop.ods_02_customer b
                  on a.customer_id = b.customer_id
    where a.order_status = '6' and bdp_day='${yesterday}'
)
insert overwrite table dws_nshop.dws_nshop_user_complainant partition (bdp_day='${yesterday}')
select t1.customer_id,
       t1.customer_natives,
       count(t1.order_id),
       sum(t1.payment_money),
       count(t1.supplier_code),
       `current_timestamp`()
from t1 group by customer_id, customer_natives;

-- 商家用户交互记录宽表 dws_nshop_supplier_user
with tmp as (
    select
        c.supplier_code,
        d.supplier_type,
        b.page_code
    from ods_nshop.dim_pub_page b
             join ods_nshop.dim_pub_product c
                  on b.page_target=c.product_code
             join ods_nshop.dim_pub_supplier d
                  on c.supplier_code=d.supplier_code
), t_view as (
    select s.supplier_code,
           s.supplier_type,
           count(v.user_id) as v_count -- 浏览次数
    from dwd_nshop.dwd_nshop_actlog_pdtview v
             join tmp s on v.target_id = s.page_code
    where v.bdp_day = '${yesterday}'
    group by s.supplier_code, s.supplier_type
),
     t_comment as (
         select s.supplier_code,
                s.supplier_type,
                count(c.user_id)   c_count,
                count(c.area_code) a_count
         from dwd_nshop.dwd_actlog_product_comment c join tmp s
                                                          on c.target_id = s.page_code
         where c.bdp_day = '${yesterday}'
         group by s.supplier_code, s.supplier_type
     )
insert
overwrite
table
dws_nshop.dws_nshop_supplier_user
partition
(
bdp_day = '${yesterday}'
)
select v.supplier_code,
       v.supplier_type,
       v_count,
       nvl(c_count, 0),
       nvl(a_count, 0),
       `current_timestamp`()
from t_view v left join t_comment c
                        on v.supplier_code = c.supplier_code;
-- 商家日流水宽表 dws_nshop_supplier_sales
insert overwrite table dws_nshop.dws_nshop_supplier_sales partition (bdp_day='${yesterday}')
select a.supplier_code,
       s.supplier_type,
       count(distinct a.customer_id),
       count(distinct a.consignee_zipcode),
       count(a.order_id),
       sum(a.payment_money),
       sum(a.district_money),
       `current_timestamp`()
from dwd_nshop.dwd_nshop_orders_details a
         join ods_nshop.dim_pub_supplier s
              on a.supplier_code = s.supplier_code
where bdp_day = '${yesterday}'
group by a.supplier_code, s.supplier_type;
-- 广告投放用户宽表 dws_nshop_release_user
insert overwrite table dws_nshop.dws_nshop_release_user partition (bdp_day = '${yesterday}')
select release_sources,
       release_category,
       count(customer_id),
       count(release_product_page),
       `current_timestamp`()
from dwd_nshop.dwd_nshop_releasedatas
where bdp_day = '${yesterday}'
group by release_sources, release_category;
-- 用户营销活动宽表 dws_nshop_user_release
insert overwrite table dws_nshop.dws_nshop_user_release partition (bdp_day = '${yesterday}')
select distinct a.customer_id,
                a.os,
                a.os_version,
                a.manufacturer,
                b.carrier,
                b.network_type,
                a.area_code,
                count(release_sources) over(partition by a.customer_id),
        `current_timestamp`()
from dwd_nshop.dwd_nshop_releasedatas a
         left join ods_nshop.ods_nshop_01_useractlog b
                   on a.device_num = b.device_num
where a.bdp_day = '${yesterday}' and b.bdp_day = '${yesterday}';